package dao;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import model.Course;
import model.UserCourse;
import service.impl.CloseConnections;

public class UserCourseDAO {
	static Connection currentConnection = null;
	static ResultSet rs = null;

	public static void save(List<UserCourse> courses) {
		PreparedStatement statement = null;

		try {
			currentConnection = ConnectionManager.getConnection();
			currentConnection.setAutoCommit(false);
			String sql = "insert into user_course values(?,?,?)";
			statement = currentConnection.prepareStatement(sql);
			for (UserCourse course : courses) {
				statement.setLong(1, course.getUserId());
				statement.setString(2, course.getCourseCode());
				statement.setLong(3, course.getSemesterId());
				statement.addBatch();
			}
			statement.executeBatch();
			currentConnection.commit();

		} catch (BatchUpdateException s) {
			System.out.println("SQL statement is not executed!" + s);
		} catch (Exception e) {
			System.out.println("Exception in connection!: " + e);
		} finally {
			CloseConnections.closeAll(rs, statement, currentConnection);
		}

	}

	public static List<Course> get(Long userId, Long semesterId) {
		PreparedStatement statement = null;
		List<Course> courses = new ArrayList<Course>();

		try {
			currentConnection = ConnectionManager.getConnection();
			String sql = "select * from course c "
					+ " inner join user_course uc on uc.course_code = c.code"
					+ " where uc.user_id = ? and uc.semester_id = ?";
			statement = currentConnection.prepareStatement(sql);
			statement.setLong(1, userId);
			statement.setLong(2, semesterId);
			rs = statement.executeQuery();
			while (rs.next()) {
				Course course = new Course();
				course.setCode(rs.getString("code"));
				course.setName(rs.getString("name"));
				courses.add(course);
			}

		} catch (Exception e) {
			System.out.println("Exception in connection!: " + e);
		} finally {
			CloseConnections.closeAll(rs, statement, currentConnection);
		}
		return courses;
	}
}
